Ex013, Table Filtering

When you want to search for certain data or you want to filter some data always
you use Query data access component, in which you can write SQL statements which
filters the tables using SQL language. Tables also can be filtered, instead of using
SQL language you can use Object Pascal language to perform any search or filter.
This example illustrates how to filter data using Table.

Exercise 013: Table Filtering

In this example we want to berform search on Phone directory table. To see how to
design Phone directory table please refere to Ex007.

1. Drop a Table, DataSource, and DBGRid then link it with Phone.db (You should know
how to do that. If you did not, then see Ex007 and Ex008).
2. Drop an Edit box and clear it's Text property content.
3. At main Form's OnCreate event write:

Table1.Open;
Table1.Filtered:= True;

4. At main Form's OnClose event write:

Table1.Filtered:= False;

5. At Table's OnFilterRecord event write:

if (Pos(UpperCase(Edit1.Text),
UpperCase(
DataSet.FieldByName('Name').AsString)) > 0)
or (Edit1.Text = '') then
Accept:= True // Display this record
else
Accept:= False; // Hide this record

6. At Edit box's OnKeyPress event write:

if Key = #13 then
Table1.Refresh;

7. Run the program and make sure that there is a data on Phone.db table. Enter any
name in Edit box or a letter of a name then press enter.

Notes:

- The most important things in table filtering is OnFilterRecord event and Filtered property.

1. OnFilterRecord This event has two parameters:

DataSet: DataSet is the Table or Query who own this event. By using this parameter
we can access current record being filtered such as:

MyText:= DataSet.FieldByName('Name').AsString;

Accept: If it was assigned to True it will retrieve current filtered record. If
it was assigned to False it will not retrieve that record, for example if you write
this in OnFilterRecord event:

Accept:= Length(DataSet.FieldByName('Name').AsString) > 8

This will retrieves only records that it's Name field content length greater than
8 characters.

2. Filtered Filtered property determine if the Table will be filtered or not. If
it was assigned to True that mean OnFilterRecord event will be applied, but if it
was False, OnFilterRecord event will be ignored and all records in the table will
be retrieved.

- OnFilterRecord event will be applied to each record in the table. Each time you
want to apply this event again to the table you must call Table's Refresh method.

See also:

Ex008: Telephone directory (Using Table)
Ex009: Telephone directory (Using Query)
Ex012: Accessing records programmatically